#!/usr/bin/env python3
"""
Correct Directory Table Creator for UmaInsight
Creates directory table based on actual histograms.xml file paths
Correctly maps metrics to their source histogram file directories
"""

import sqlite3
import sys
import os
from datetime import datetime
from typing import List, Dict, Set, Tuple

def get_database_path() -> str:
    """Get the path to the SQLite database"""
    current_dir = os.path.dirname(os.path.abspath(__file__))
    backend_data_dir = os.path.join(current_dir, '..', 'backend', 'data')
    db_path = os.path.join(backend_data_dir, 'uma_insight.db')
    return db_path

def extract_histogram_directories(cursor) -> List[str]:
    """Extract unique directories from metrics file_path field"""
    print("正在从metrics的file_path字段提取histograms目录...")

    cursor.execute("""
        SELECT DISTINCT
            CASE
                WHEN file_path LIKE '%/histograms.xml'
                THEN SUBSTR(file_path, 1, INSTR(file_path, '/histograms.xml') - 1)
                ELSE NULL
            END as histogram_directory
        FROM metrics
        WHERE file_path IS NOT NULL
          AND file_path LIKE '%/histograms.xml%'
          AND file_path != ''
        ORDER BY histogram_directory
    """)

    directories = [row[0] for row in cursor.fetchall() if row[0] is not None]
    print(f"提取到 {len(directories)} 个唯一的histograms目录")
    return directories

def drop_old_directory_tables(cursor, connection):
    """Drop old incorrect directory tables"""
    print("正在删除旧的directory表...")

    cursor.execute("DROP TABLE IF EXISTS metric_directories")
    cursor.execute("DROP TABLE IF EXISTS directories")

    connection.commit()
    print("旧表已删除")

def create_correct_directory_table(cursor, connection):
    """Create the correct directories table for histogram paths"""
    print("正在创建正确的directories表...")

    cursor.execute("""
        CREATE TABLE directories (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            path VARCHAR(500) NOT NULL UNIQUE,
            name VARCHAR(255) NOT NULL,
            metric_count INTEGER DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    """)

    # Create indexes for performance
    cursor.execute("CREATE INDEX idx_directories_path ON directories(path)")
    cursor.execute("CREATE INDEX idx_directories_name ON directories(name)")
    cursor.execute("CREATE INDEX idx_directories_metric_count ON directories(metric_count)")
    cursor.execute("CREATE INDEX idx_directories_list_performance ON directories(metric_count DESC, name)")

    print("正确的directories表创建成功")

def create_metric_directory_association_table(cursor, connection):
    """Create the metric_directories association table"""
    print("正在创建metric_directories关联表...")

    cursor.execute("""
        CREATE TABLE metric_directories (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            metric_id INTEGER NOT NULL,
            directory_id INTEGER NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (metric_id) REFERENCES metrics(id) ON DELETE CASCADE,
            FOREIGN KEY (directory_id) REFERENCES directories(id) ON DELETE CASCADE,
            UNIQUE(metric_id, directory_id)
        )
    """)

    # Create indexes for performance
    cursor.execute("CREATE INDEX idx_metric_directories_metric_id ON metric_directories(metric_id)")
    cursor.execute("CREATE INDEX idx_metric_directories_directory_id ON metric_directories(directory_id)")
    cursor.execute("CREATE INDEX idx_metric_directories_composite ON metric_directories(directory_id, metric_id)")

    print("metric_directories关联表创建成功")

def extract_directory_name(path: str) -> str:
    """Extract directory name from full path"""
    # For path like "tools/metrics/histograms/metadata/accessibility",
    # extract "accessibility" as the name
    parts = path.split('/')
    return parts[-1] if parts else path

def populate_correct_directories(cursor, connection, directories: List[str]):
    """Populate directories table with histogram directories"""
    print("正在填充正确的directories表...")

    for directory_path in directories:
        directory_name = extract_directory_name(directory_path)
        cursor.execute("""
            INSERT OR IGNORE INTO directories (path, name) VALUES (?, ?)
        """, (directory_path, directory_name))

    connection.commit()
    print(f"成功插入 {len(directories)} 个histograms directory记录")

def update_directory_metric_counts(cursor, connection):
    """Update metric_count for each directory"""
    print("正在更新各directory的metric数量...")

    cursor.execute("""
        UPDATE directories
        SET metric_count = (
            SELECT COUNT(*)
            FROM (
                SELECT
                    CASE
                        WHEN m.file_path LIKE '%/histograms.xml'
                        THEN SUBSTR(m.file_path, 1, INSTR(m.file_path, '/histograms.xml') - 1)
                        ELSE NULL
                    END as histogram_dir,
                    m.id
                FROM metrics m
                WHERE m.file_path IS NOT NULL
                  AND m.file_path LIKE '%/histograms.xml%'
                  AND m.file_path != ''
            ) metric_dirs
            WHERE metric_dirs.histogram_dir = directories.path
        ),
        updated_at = CURRENT_TIMESTAMP
    """)

    connection.commit()
    print("Directory metric数量更新完成")

def populate_metric_directories(cursor, connection):
    """Populate metric_directories association table"""
    print("正在填充metric_directories关联表...")

    # First, get directory mapping
    cursor.execute("SELECT id, path FROM directories")
    directory_map = {row[1]: row[0] for row in cursor.fetchall()}

    # Get all metrics with their histogram directories
    cursor.execute("""
        SELECT id,
               CASE
                   WHEN file_path LIKE '%/histograms.xml'
                   THEN SUBSTR(file_path, 1, INSTR(file_path, '/histograms.xml') - 1)
                   ELSE NULL
               END as histogram_dir
        FROM metrics
        WHERE file_path IS NOT NULL
          AND file_path LIKE '%/histograms.xml%'
          AND file_path != ''
    """)

    metrics_with_dirs = cursor.fetchall()

    # Prepare insertion data
    associations = []
    for metric_id, histogram_dir in metrics_with_dirs:
        if histogram_dir in directory_map:
            directory_id = directory_map[histogram_dir]
            associations.append((metric_id, directory_id))

    # Batch insert associations
    cursor.executemany("""
        INSERT OR IGNORE INTO metric_directories (metric_id, directory_id)
        VALUES (?, ?)
    """, associations)

    connection.commit()
    print(f"成功创建 {len(associations)} 条metric-directory关联记录")

def verify_correct_data_integrity(cursor):
    """Verify that all directories and associations are created correctly"""
    print("正在验证正确的数据完整性...")

    # Check directories count
    cursor.execute("SELECT COUNT(*) FROM directories")
    dir_count = cursor.fetchone()[0]

    # Check metric-directories associations count
    cursor.execute("SELECT COUNT(*) FROM metric_directories")
    assoc_count = cursor.fetchone()[0]

    # Check metrics with histogram paths
    cursor.execute("""
        SELECT COUNT(*) FROM metrics
        WHERE file_path IS NOT NULL
          AND file_path LIKE '%/histograms.xml%'
          AND file_path != ''
    """)
    metrics_with_histograms = cursor.fetchone()[0]

    # Check total metrics
    cursor.execute("SELECT COUNT(*) FROM metrics")
    total_metrics = cursor.fetchone()[0]

    print(f"✅ Histogram Directory数量: {dir_count}")
    print(f"✅ Metric-Directory关联数量: {assoc_count}")
    print(f"✅ 有histograms.xml路径的Metrics数量: {metrics_with_histograms}")
    print(f"✅ 总Metrics数量: {total_metrics}")
    print(f"✅ 覆盖率: {(assoc_count/metrics_with_histograms)*100:.1f}%" if metrics_with_histograms > 0 else "N/A")

    # Show top directories by metric count
    cursor.execute("""
        SELECT path, name, metric_count FROM directories
        WHERE metric_count > 0
        ORDER BY metric_count DESC
        LIMIT 15
    """)

    top_dirs = cursor.fetchall()
    print(f"\n📊 Top 15 Histogram Directory by Metric数量:")
    for i, (path, name, count) in enumerate(top_dirs, 1):
        print(f"   {i:2d}. {name:<20} ({count:>4} metrics)")
        print(f"       路径: {path}")

def show_directory_distribution(cursor):
    """Show distribution of directories"""
    print(f"\n📂 Directory类型分布:")

    # Analyze directory depth and patterns
    cursor.execute("""
        SELECT
            CASE
                WHEN path LIKE 'tools/metrics/histograms/metadata/%' THEN 'metadata'
                WHEN path LIKE 'tools/metrics/histograms/%' THEN 'other'
                ELSE 'unknown'
            END as dir_type,
            COUNT(*) as count,
            SUM(metric_count) as total_metrics
        FROM directories
        GROUP BY dir_type
        ORDER BY count DESC
    """)

    types = cursor.fetchall()
    for dir_type, count, total_metrics in types:
        print(f"   {dir_type}: {count} directories, {total_metrics} metrics")

def main():
    """Main function to create correct directory tables and populate them"""
    try:
        print("=== UmaInsight 正确Directory表创建工具 ===")
        print(f"开始时间: {datetime.now()}")

        # Connect to database
        db_path = get_database_path()
        print(f"连接数据库: {db_path}")

        connection = sqlite3.connect(db_path)
        cursor = connection.cursor()

        # Step 1: Extract histogram directories from metrics file_path
        directories = extract_histogram_directories(cursor)

        if len(directories) == 0:
            print("❌ 未找到histograms目录，请检查metrics表的file_path字段")
            sys.exit(1)

        # Step 2: Drop old incorrect tables
        drop_old_directory_tables(cursor, connection)

        # Step 3: Create correct tables
        create_correct_directory_table(cursor, connection)
        create_metric_directory_association_table(cursor, connection)

        # Step 4: Populate correct data
        populate_correct_directories(cursor, connection, directories)
        update_directory_metric_counts(cursor, connection)
        populate_metric_directories(cursor, connection)

        # Step 5: Verify and show results
        verify_correct_data_integrity(cursor)
        show_directory_distribution(cursor)

        print(f"\n✅ 正确的Directory表创建完成！")
        print(f"现在directories表存储的是histograms.xml文件的上级目录路径")
        print(f"完成时间: {datetime.now()}")

        connection.close()

    except Exception as e:
        print(f"❌ 错误: {e}")
        sys.exit(1)

if __name__ == "__main__":
    main()